package com.xinan.system.generat;


import com.jfinal.plugin.activerecord.Config;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.DbKit;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
/**
 * 
 * 根据数据库表的元数据(metadata)创建Table对象
 * 
 * <pre>
 * getTable(sqlName) : 根据数据库表名,得到table对象
 * getAllTable() : 搜索数据库的所有表,并得到table对象列表
 * </pre>
 * @author badqiu
 * @email badqiu(a)gmail.com
 */
public class TableFactory {
	private static TableFactory instance = null;

	private TableFactory() {
	}
	
	public synchronized static TableFactory getInstance() {
		if(instance == null) instance = new TableFactory();
		return instance;
	}

	public String getCatalog() {
		return null;
	}

	public String getSchema() {
		return null;
	}

	
	public List<Table> getAllTables() {
		try {
			Config config = getConfig();
			Connection conn = config.getConnection();
			return getAllTables(conn);
		}catch(Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	public Table getTable(String tableName) {
		return getTable(getSchema(),tableName);
	}

	private Table getTable(String schema,String tableName) {
		return getTable(getCatalog(),schema,tableName);
	}
	
	private Table getTable(String catalog,String schema,String tableName) {
		Table t = null;
		try {
			t = _getTable(catalog,schema,tableName);
			if(t == null && !tableName.equals(tableName.toUpperCase())) {
				t = _getTable(catalog,schema,tableName.toUpperCase());
			}
			if(t == null && !tableName.equals(tableName.toLowerCase())) {
				t = _getTable(catalog,schema,tableName.toLowerCase());
			}
		}catch(Exception e) {
			throw new RuntimeException(e);
		}
		if(t == null) {
			throw new NotFoundTableException("not found table with give name:"+tableName);
		}
		return t;
	}
	
	public static class NotFoundTableException extends RuntimeException {
		private static final long serialVersionUID = 5976869128012158628L;
		public NotFoundTableException(String message) {
			super(message);
		}
	}

	private Table _getTable(String catalog,String schema,String tableName) throws SQLException {
	    if(tableName== null || tableName.trim().length() == 0) 
	         throw new IllegalArgumentException("tableName must be not empty");
	    Config config = getConfig();
		Connection conn = config.getConnection();
		DatabaseMetaData dbMetaData = conn.getMetaData();
		ResultSet rs = dbMetaData.getTables(catalog, schema, tableName, null);
		while(rs.next()) {
			Table table = createTable(conn, rs);
			return table;
		}
		conn.close();
		return null;
	}

	private Table createTable(Connection conn, ResultSet rs) throws SQLException {
		String realTableName = null;
		try {
            realTableName = rs.getString("TABLE_NAME");
			String remarks = rs.getString("REMARKS");
//			if(remarks == null && DbKit.getDialect().isOracle()) {
//				remarks = getOracleTableComments(realTableName);
//			}
//			
			Table table = new Table();
			table.setSqlName(realTableName);
			table.setRemarks(remarks);

			retriveTableColumns(table,conn);
			return new Table(table);
		}catch(SQLException e) {
			throw new RuntimeException("create table object error,tableName:"+realTableName,e);
		}
	}
	
	private List<Table> getAllTables(Connection conn) throws SQLException {
		DatabaseMetaData dbMetaData = conn.getMetaData();
		ResultSet rs = dbMetaData.getTables(getCatalog(), getSchema(), null, null);
		List<Table> tables = new ArrayList();
		while(rs.next()) {
            String realTableName = rs.getString("TABLE_NAME");
            String remarks = rs.getString("REMARKS");
//            if(remarks == null && DbKit.getDialect().isOracle()) {
//                remarks = getOracleTableComments(realTableName);
//            }
            Table table = new Table();
            table.setSqlName(realTableName);
            table.setRemarks(remarks);
			tables.add(table);
		}
		conn.close();//关闭数据库连接
		return tables;
	}
	   
	private DatabaseMetaData getMetaData() throws SQLException {
		Config config = getConfig();
	    Connection conn = config.getConnection();
        DatabaseMetaData dbMetaData = conn.getMetaData();
        conn.close();
		return dbMetaData;
	}
	
	private void retriveTableColumns(Table table,Connection conn) throws SQLException {

	      List primaryKeys = getTablePrimaryKeys(table);
	      table.setPkColumns(primaryKeys);
	      
	      // get the indices and unique columns
	      List indices = new LinkedList();
	      // maps index names to a list of columns in the index
	      Map uniqueIndices = new HashMap();
	      // maps column names to the index name.
	      Map uniqueColumns = new HashMap();
	      ResultSet indexRs = null;

	      try {
             indexRs = getMetaData().getIndexInfo(getCatalog(), getSchema(), table.getSqlName(), false, true);
	         while (indexRs.next()) {
	            String columnName = indexRs.getString("COLUMN_NAME");
	            if (columnName != null) {
	               indices.add(columnName);
	            }

	            // now look for unique columns
	            String indexName = indexRs.getString("INDEX_NAME");
	            boolean nonUnique = indexRs.getBoolean("NON_UNIQUE");

	            if (!nonUnique && columnName != null && indexName != null) {
	               List l = (List)uniqueColumns.get(indexName);
	               if (l == null) {
	                  l = new ArrayList();
	                  uniqueColumns.put(indexName, l);
	               }
	               l.add(columnName);
	               uniqueIndices.put(columnName, indexName);
	            }
	         }
	      } catch (Throwable t) {
	         // Bug #604761 Oracle getIndexInfo() needs major grants
	         // http://sourceforge.net/tracker/index.php?func=detail&aid=604761&group_id=36044&atid=415990
	      } finally {
              conn.close();
	      }

	      List columns = getTableColumns(table, primaryKeys, indices, uniqueIndices, uniqueColumns);

	      for (Iterator i = columns.iterator(); i.hasNext(); ) {
	         Column column = (Column)i.next();
	         table.addColumn(column);
	      }
	}

	private List getTableColumns(Table table, List primaryKeys, List indices, Map uniqueIndices, Map uniqueColumns) throws SQLException {
		// get the columns
	      List columns = new LinkedList();
	      ResultSet columnRs = getColumnsResultSet(table);
	      
	      while (columnRs.next()) {
	         int sqlType = columnRs.getInt("DATA_TYPE");
	         String sqlTypeName = columnRs.getString("TYPE_NAME");
	         String columnName = columnRs.getString("COLUMN_NAME");
	         String columnDefaultValue = columnRs.getString("COLUMN_DEF");
	         
	         String remarks = columnRs.getString("REMARKS");
//	         if(remarks == null && DbKit.getDialect().isOracle()) {
//	        	 remarks = getOracleColumnComments(table.getSqlName(), columnName);
//	         }
	         
	         // if columnNoNulls or columnNullableUnknown assume "not nullable"
	         boolean isNullable = (DatabaseMetaData.columnNullable == columnRs.getInt("NULLABLE"));
	         int size = columnRs.getInt("COLUMN_SIZE");
	         int decimalDigits = columnRs.getInt("DECIMAL_DIGITS");

	         boolean isPk = primaryKeys.contains(columnName);
	         boolean isIndexed = indices.contains(columnName);
	         String uniqueIndex = (String)uniqueIndices.get(columnName);
	         List columnsInUniqueIndex = null;
	         if (uniqueIndex != null) {
	            columnsInUniqueIndex = (List)uniqueColumns.get(uniqueIndex);
	         }

	         boolean isUnique = columnsInUniqueIndex != null && columnsInUniqueIndex.size() == 1;

	         Column column = new Column(
	               table,
	               sqlType,
	               sqlTypeName,
	               columnName,
	               size,
	               decimalDigits,
	               isPk,
	               isNullable,
	               isIndexed,
	               isUnique,
	               columnDefaultValue,
	               remarks);
	         columns.add(column);
	    }
	    columnRs.close();
		return columns;
	}
	
	private ResultSet getColumnsResultSet(Table table) throws SQLException {
		ResultSet columnRs = getMetaData().getColumns(getCatalog(), getSchema(), table.getSqlName(), null);
		return columnRs;
	}

	private List<String> getTablePrimaryKeys(Table table) throws SQLException {
		// get the primary keys
	      List primaryKeys = new LinkedList();
	      ResultSet primaryKeyRs = getMetaData().getPrimaryKeys(getCatalog(), getSchema(), table.getSqlName());
	      while (primaryKeyRs.next()) {
	         String columnName = primaryKeyRs.getString("COLUMN_NAME");
	         primaryKeys.add(columnName);
	      }
	      primaryKeyRs.close();
		return primaryKeys;
	}

	private String getOracleTableComments(String table)  {
		String sql = "SELECT comments FROM user_tab_comments WHERE table_name='"+table+"'";
		return Db.queryStr(sql);
	}

	private String getOracleColumnComments(String table,String column)  {
		String sql = "SELECT comments FROM user_col_comments WHERE table_name='"+table+"' AND column_name = '"+column+"'";
		return Db.queryStr(sql);
	}
	
	private Config getConfig() {
		return DbKit.getConfig();
	}
}
